package com.mms.dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mms.config.HibernateSessionFactory;
import com.mms.entity.Department;
import com.mms.entity.Meeting;
import com.mms.entity.MeetingRoom;
import com.mms.entity.Staff;
import com.mms.util.HibernateUtil;

public class PersonManagementDao {
	
	 //检测用户密码是否正确
	@SuppressWarnings("unchecked")
    public int checkUser(String userName, String password){
		List<Integer> result ;
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql="select s_id from staff where s_name=? and password=?";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setParameter(0, userName);
            query.setParameter(1, password);
            result=query.list();
            if (result.size()>0) {
                return result.get(0);
            }
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        } 
        return -1;
	}
	
	public boolean isUserExist(String userName) {
	    List<BigInteger> result ;
        int num = 0;
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select count(*) from staff where s_name=?";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setString(0, userName);
            result=query.list();
            num=Integer.parseInt(result.get(0).toString());
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        }
        
        return num>0?true:false;
    }
	
	 //检测是否验证通过
    public boolean validateState(int staffID){
        boolean isPass=false;
        List<Integer> result ;
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql="select state from staff where s_id=?";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setParameter(0, staffID);
            result=query.list();
            if (result.get(0).equals(1)) {
                isPass= true;
            }
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        } 
        return isPass;
    }
	
    
    //检测是否验证通过
    public boolean isAdminister(int staffID){
        boolean isPass=false;
        List<Integer> result ;
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql="select admin_flag from staff where s_id=?";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setParameter(0, staffID);
            result=query.list();
            if (result.get(0).equals(1)) {
                isPass= true;
            }
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        } 
        return isPass;
    }
    
	public boolean registerStaff(Staff staff){
        Session session = HibernateUtil.getSession(); 
        try{ 
        Transaction transaction=session.beginTransaction();
        session.save(staff); 
        transaction.commit();
        return true; 
        }catch(Exception ex){ 
            ex.printStackTrace();
        return false; 
        }finally{ 
        session.close(); 
        } 
	}

	

    public List<Staff> getStaffs(){
    List<Object[]> objs;
    List<Staff> personStaffs =new ArrayList<Staff>();
    Session session = HibernateUtil.getSession(); 
    try{ 
        String sql="select s_id,de_id,s_name from staff where state = 1";
        Transaction transaction=session.beginTransaction();
        Query query=session.createSQLQuery(sql);
        objs=query.list();
        for (Object[] objects : objs) {
            Staff staff =new Staff();
            staff.setSId(Integer.parseInt(objects[0].toString()));
            Department department=new Department();
            department.setDeId(Integer.parseInt(objects[1].toString()));
            staff.setDepartment(department);
            staff.setSName(objects[2].toString());
            personStaffs.add(staff);
        }
        transaction.commit();
    }catch(Exception ex){ 
        ex.printStackTrace(); 
    }finally{ 
    session.close(); 
    } 
    return personStaffs;
    
    }
    
    
    public boolean isStaffFree(int s_id,String startTime,String endTime){
        boolean isFree= true;
        List<BigInteger> result ;
        int num = 0;
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select count(*) from invitation,meeting where invitation.topic=meeting.topic and state = 0 and invitation.s_id = ? and ((start_time < ? and end_time > ?) or  (start_time < ? and end_time > ?) or (start_time > ? and end_time <?) )";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setInteger(0, s_id);
            query.setString(1, startTime);
            query.setString(2, startTime);
            query.setString(3, endTime);
            query.setString(4, endTime);
            query.setString(5, startTime);
            query.setString(6, endTime);
           
            result=query.list();
            num=Integer.parseInt(result.get(0).toString());
            if (num>0) {
                isFree=false;
            }
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        }
        return isFree;
        
        
    }
    
	
	
    /*/ 同意
	
	public void approve(int id){
		 Session session = HibernateUtil.getSession();
		try{
			Transaction transaction = session.beginTransaction();
			String sql = "update staff set state=1 where s_id=?";
			Query query = session.createSQLQuery(sql);
			query.setInteger(0,id);
			transaction.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
	}*/
	

    
      // 修改员工信息
    
    public boolean modifyPerson(Staff staff){
        	Session session = HibernateUtil.getSession();
        try{
            Transaction transaction = session.beginTransaction();
            session.update(staff);
            transaction.commit();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }finally{
        	session.close();
        }
    }
    
    
    /*/ * 离职
     
    public void dismissionPerson(int s_id){
        	Session session = HibernateUtil.getSession();
        try{
			Transaction transaction = session.beginTransaction();
			
        }catch(Exception e){
            e.printStackTrace();
        }
    }*/

    
     //* 得到员工全部信息
     
    public List<Staff> getStaffs(int Spage){
    	 Session session = HibernateUtil.getSession();
    	 List<Object[]> objs;
         List<Staff> personStaffs =new ArrayList<Staff>();
         try {
             String sql="select * from staff,department where staff.de_id=department.de_id limit ?,6";
             Query query = session.createSQLQuery(sql);
             Transaction transaction = session.beginTransaction();
             query.setInteger(0,Spage*6);
             objs = query.list();
             for (Object[] objects : objs) {
             	Staff staff =new Staff();
             	Department department = new Department();
             	department.setDeId(Integer.parseInt(objects[3].toString()));
             	department.setDeName(objects[11].toString());
             	staff.setSId((Integer.parseInt(objects[0].toString())));
             	staff.setSName(objects[1].toString());
             	staff.setSex(Integer.parseInt(objects[2].toString()));
             	staff.setDepartment(department);
             	staff.setAdminFlag(Integer.parseInt(objects[4].toString()));
             	staff.setPhone(objects[5].toString());
             	staff.setPassword(objects[6].toString());
             	staff.setPosition(objects[7].toString());
             	staff.setState(Integer.parseInt(objects[8].toString()));
             	if(objects[9] == null){
             		staff.setReCode("");
             	}else {
             		staff.setReCode(objects[9].toString());
				}
             	personStaffs.add(staff);
             }
             transaction.commit();
         } catch (Exception e) {
             e.printStackTrace();
         }finally{
        	 session.close();
         }
         return personStaffs;
     }
    
    
    
   
    public int getcount(){
    	 Session session = HibernateUtil.getSession();
    	 List<BigInteger> num;
    	 int nums = 0;
		 try{
			 String sql = "select count(s_id) from staff";
			 Transaction transaction = session.beginTransaction();
			 Query query = session.createSQLQuery(sql);
			 num = query.list();
			 nums = Integer.parseInt(num.get(0).toString());
			 transaction.commit();
		 }catch(Exception e){
			 e.printStackTrace();
		 }finally{
			 session.close();
		 }
		 return nums;
	 }
    
   
    public List<Staff> getSearchStaffs(String searchMes){
    	Session session = HibernateUtil.getSession();
   	 List<Object[]> objs;
        List<Staff> personStaffs =new ArrayList<Staff>();
        try {
        	String sql="select * from staff,department where staff.de_id=department.de_id and s_name like '%"+searchMes+"%'";
            Query query = session.createSQLQuery(sql);
            Transaction transaction = session.beginTransaction();
            objs = query.list();
            for (Object[] objects : objs) {
            	Staff staff =new Staff();
            	Department department = new Department();
            	department.setDeId(Integer.parseInt(objects[3].toString()));
            	department.setDeName(objects[11].toString());
            	staff.setSId((Integer.parseInt(objects[0].toString())));
            	staff.setSName(objects[1].toString());
            	staff.setSex(Integer.parseInt(objects[2].toString()));
            	staff.setDepartment(department);
            	staff.setAdminFlag(Integer.parseInt(objects[4].toString()));
            	staff.setPhone(objects[5].toString());
            	staff.setPassword(objects[6].toString());
            	staff.setPosition(objects[7].toString());
            	staff.setState(Integer.parseInt(objects[8].toString()));
            	if(objects[9] == null){
            		staff.setReCode("");
            	}else {
            		staff.setReCode(objects[9].toString());
				}
            	personStaffs.add(staff);
            }
            transaction.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
       	 session.close();
        }
        return personStaffs;
    }
}
